/**
 * 输入数据集 input类型 只能作为source,不能作为target
 * config:{table:'comp_lib',limit:1000}
 * id:["EfeINDdG"]
 */
const handleInputEtl = function (id, config) {
  const { table, limit, fields } = config
  let filedArr = fields.map((item) => {
    return `${table}.${item.COLUMN_NAME} as ${item.alias}`
  })
  let limitSql = ''
  if (limit) {
    limitSql = `limit ${limit}`
  }
  let sql = `${id} as (select ${filedArr.join(',')} from ${table} ${limitSql})`
  return sql
}
/**
 * 筛选数据行 filter类型 source有且只有一个
 * from: source_id 查询的临时表
 * config:{condition: [{field:'id',value:"",function:''}]}
 *  function:{
 *    等于: '=',
 *    不等于:'!=',
 *    小于: '<',
 *    小于等于: '<',
 *    大于: '>',
 *    大于等于: '<',
 *    包含:'like',
 *    不包含:'notLike'
 *    开始以:'%like',
 *    结束以:'like%',
 *    开始不以:'not %like',
 *    结束不以:'not like%',
 *    是空:'empty',
 *    不是空:'not empty',
 *    是null:'null',
 *    不是null:'notNull'
 *  }
 */
const handleFilterEtl = function (id, config, children) {
  let table = children[0].id
  let conditionSql = ['where 1=1']
  config.filter.map((item) => {
    switch (item.function.trim()) {
      case '=':
      case '!=':
      case '>':
      case '>=':
      case '<':
      case '<=':
        if (typeof item.val === 'number') {
          conditionSql.push(
            `and ${table}.${item.field} ${item.function} ${item.val}`
          )
        } else {
          conditionSql.push(
            `and ${table}.${item.field} ${item.function} '${item.val}'`
          )
        }
        break
      case 'like':
        conditionSql.push(`and ${table}.${item.field} like '%${item.val}%'`)
        break
      case 'notLike':
        conditionSql.push(`and ${table}.${item.field} not like '%${item.val}%'`)
        break
      case '%like':
        conditionSql.push(`and ${table}.${item.field} like '%${item.val}'`)
        break
      case 'not %like':
        conditionSql.push(`and ${table}.${item.field} not like '%${item.val}'`)
        break
      case 'not like%':
        conditionSql.push(`and ${table}.${item.field} not like '${item.val}%'`)
        break
      case 'empty':
        conditionSql.push(`and trim(${table}.${item.field}) = ''`)
        break
      case 'not empty':
        conditionSql.push(`and trim(${table}.${item.field}) != ''`)
        break
      case 'null':
        conditionSql.push(`and ${table}.${item.field} is NULL`)
        break
      case 'notNull':
        conditionSql.push(`and ${table}.${item.field} is NOT NULL`)
        break
      default:
        break
    }
  })
  let sql = `${id} as (select * from ${table} ${conditionSql.join(' ')})`
  return sql
}

/**
 * 列計算 compute 类型 source 有且仅有一个
 * config:{
 *          computes:[{first:"",last:"",alias:"",deleteColumn:""}],
 *          fields:[],
 *        }
 * id:["EfeINDdG"]
 */
const handleComputeEtl = function (id, config, children) {
  const { computes, fields } = config
  let table = children[0].id
  let computeSql = []
  fields.map((item) => {
    let computeItem = computes.find((compute) => compute.alias === item.alias)
    if (computeItem) {
      if (`${computeItem.function}` !== '/') {
        computeSql.push(
          `(${table}.${computeItem.first} ${computeItem.function} ${table}.${computeItem.last}) as ${item.alias}`
        )
      } else {
        computeSql.push(
          `(case when ${table}.${computeItem.last} <>0 then ${table}.${computeItem.first} ${computeItem.function} ${table}.${computeItem.last} else 0 end) as ${item.alias}`
        )
      }
    } else {
      computeSql.push(`${table}.${item.alias}`)
    }
  })
  sql = `${id} as (select ${computeSql.join(',')} from ${table})`
  return sql
}

/**
 * 列合并 merge 类型 source 有且仅有一个
 * config:{
 *          rowName:"",
 *          rows:["name","name1"],
 *          fields:["n1","n2","n3"], // 原有数据列
 *          deleteRows:false
 *          separator:"", // '' ',' ';' 'tab' ' '
 *        }
 * id:["EfeINDdG"]
 */
const handleMergeEtl = function (id, config, children) {
  const { rowName, rows, separator, fields } = config
  let table = children[0].id
  let columns = fields
    .filter((item) => {
      return item.alias !== rowName
    })
    .map((item) => {
      return `${table}.${item.alias}`
    })
  sql = `${id} as (select ${columns.join(
    ','
  )},concat_ws('${separator}',${rows.join(',')}) as ${rowName} from ${table})`
  return sql
}
/**
 * 选择列 select类型 source有且只有一个
 * config:{fields:["alias","comp_id",...]}
 * id:["EfeINDdG"]
 */
const handleSelectEtl = function (id, config, children) {
  const { fields } = config
  let table = children[0].id
  const selectSql = fields.map((item) => {
    return `${table}.${item.alias}`
  })
  sql = `${id} as (select ${selectSql.join(',')} from ${table})`
  return sql
}
/**
 * 值转换 source有且只有一个
 * config:{field:'id',rules:[{value:"",text:""}]}
 */
const handleValueChangeEtl = function (id, config, children) {
  const { selectFields, fields } = config
  const tempSql = children[0].from + 'temp'
  let code = `(case `
  selectFields.map((item) => {
    code += `when find_in_set(${item.value}, ${tempSql}.${fields}) then '${item.text}'`
  })
  code += `end) as ${fields}`
  sql = `${id} as (select *,${code} from ${children[0].from} ${tempSql}),` + sql
  return sql
}
/**
 * null值替换 source有且只有一个
 * fields:[] psql : COALESCE mysql : IFNULL oracle: NVL
 * config:{isnull:[{field:'',value:''}...]}
 */
const handleIsNullEtl = function (id, config, children, db_type) {
  const { fields, isnull } = config
  const tableFrom = children[0].from
  const isnullFields = fields.map((item) => {
    let inuSql = ''
    let inuItem = isnull.find((inu) => inu.field === item.alias)
    if (inuItem) {
      let value =
        typeof inuItem.value === 'number' ? inuItem.value : `'${inuItem.value}'`
      switch (db_type) {
        case 'MySQL':
          inuSql = `IFNULL(${tableFrom}.${inuItem.field},${value}) as ${inuItem.field}`
          break
        case 'PostgreSQL':
          inuSql = `COALESCE(${tableFrom}.${inuItem.field},${value}) as ${inuItem.field}`
          break
        case 'Oracle':
          inuSql = `NVL(${tableFrom}.${inuItem.field},${value}) as ${inuItem.field}`
          break
        default:
          break
      }
      return inuSql
    } else {
      return `${tableFrom}.${item.alias}`
    }
  })
  sql = `${id} as (select ${isnullFields.join(',')} from ${tableFrom})`
  return sql
}
/**
 * 去重 source有且只有一个
 * config:{distinct:['id','name',...]} 去重字段 数组类型
 */
const handleDistinctEtl = function (id, config, children, db_type) {
  const { distinct } = config
  const tableFrom = children[0].from
  let sql = ''
  switch (db_type) {
    case 'MySQL':
      sql = `${id} as (select * from ${tableFrom} group by ${distinct.join(
        ','
      )})`
      break
    case 'PostgreSQL':
      sql = `${id} as (select distinct on(${distinct.join(
        ','
      )}) * from ${tableFrom})`
      break
    case 'Oracle':
      let distinctSql = distinct.map((item) => {
        return `tmp_${id}.${item}`
      })
      sql = `${id} as (select * from (
        select tmp_${id}.*, row_number() over(partition by ${distinctSql} order by rownum) rn from ${tableFrom} tmp_${id}
    ) where rn = 1)`
      break
    default:
      break
  }
  return sql
}
/**
 * 排序 source有且只有一个
 * config:{order:[{field: "", order: "ASC"}]} 排序字段 正序/倒序
 */
const handleOrderEtl = function (id, config, children) {
  const { fields, order } = config
  const tableFrom = children[0].from
  let field = fields.map((item) => {
    return `${tableFrom}.${item.alias}`
  })
  let orderSql = order
    .map((item) => {
      return `${tableFrom}.${item.field} ${item.order}`
    })
    .join(',')
  let sql = `${id} as (select ${field.join(
    ','
  )} from ${tableFrom} order by ${orderSql})`
  return sql
}
/**
 * 连接
 * config:{join:{type:"",source:"",origin:"",},fields:{}}
 */
const handleJoinEtl = function (id, config, sourceNode, originNode) {
  const { join, fields } = config
  let field = fields.map((item) => {
    return `${id}_${item.join}.${item.COLUMN_NAME} as ${item.alias}`
  })
  let sql = `${id} as (select ${field.join(',')} from ${
    sourceNode.id
  } ${id}_source ${join.type} ${originNode.id} ${id}_origin on ${id}_source.${
    join.source
  } = ${id}_origin.${join.origin})`
  return sql
}

const handleUnionEtl = function (id, config, sourceNode, originNode) {
  const { union } = config
  let originFields = []
  let sourceFields = []
  union.forEach((item) => {
    originFields.push(`${originNode.id}.${item.originField} as ${item.alias}`)
    sourceFields.push(`${sourceNode.id}.${item.sourceField} as ${item.alias}`)
  })
  let sql = `${id} as (select ${sourceFields.join(',')} from ${
    sourceNode.id
  } union all select ${originFields.join(',')} from ${originNode.id})`
  return sql
}

const handleConstEtl = function (id, config, children) {
  const { fields, con } = config
  let fieldSql = []
  fields.forEach((item) => {
    if (con.rowName === item.alias) {
      fieldSql.push(`${con.value} as ${item.alias}`)
    } else {
      fieldSql.push(`${children[0].from}.${item.alias}`)
    }
  })
  let sql = `${id} as (select ${fieldSql.join(',')} from ${children[0].from}) `
  return sql
}

/**
 * 自定义行转列
 * @param {*} id
 * @param {*} config
 * @param {*} children
 */
const handleCongroupEtl = function (id, config, children) {
  const { fields, groupName, congroup, alias, type } = config
  let fieldSql = []
  congroup.forEach((item) => {
    let groupName =
      typeof item.groupName === 'number'
        ? item.groupName
        : `'${item.groupName}'`
    let value = typeof item.value === 'number' ? item.value : `'${item.value}'`
    fieldSql.push(`select ${groupName} as "name",${value} as "value"`)
  })
  let temp_sql = `${id}_temp_table as (${fieldSql.join(' UNION all ')})`
  let aliasSql = []
  fields.forEach((item) => {
    if (alias === item.alias) {
      aliasSql.push(`${id}_temp_table.value as ${item.alias}`)
    } else {
      aliasSql.push(`${children[0].from}.${item.alias}`)
    }
  })
  let sql = `${temp_sql},${id} as (select ${aliasSql.join(',')} from ${
    children[0].from
  } left join ${id}_temp_table on ${id}_temp_table.name = ${
    children[0].from
  }.${groupName}) `
  return sql
}
/**
 * 行转列
 * @param {*} id
 * @param {*} config
 * @param {*} children
 */
const handleRToCEtl = function (id, config, children) {
  const { nameColumn, dataColumn, groupName, dataRtc, fields } = config
  let caseSql = []
  let table = children[0].from
  dataRtc.map((item) => {
    caseSql.push(
      `select ${groupName}, '${item.alias}' ${nameColumn}, ${item.field} as ${dataColumn} from ${table}`
    )
  })
  let sql = `${id} as (${caseSql.join(
    ' UNION '
  )} group by ${groupName},${nameColumn})`
  return sql
}

/**
 * 列转行
 * @param {*} id
 * @param {*} config
 * @param {*} children
 */
const handleCToREtl = function (id, config, children) {
  const { changeColumn, changeValue, groupName, fields, groupType } = config
  let etcSql = `SELECT ${groupName},`
  let caseSql = []
  fields.map((item) => {
    if (item.alias !== groupName) {
      caseSql.push(
        `${groupType}(CASE ${children[0].from}.${changeColumn} WHEN '${item.alias}' THEN ${changeValue} ELSE 0 END) as "${item.alias}"`
      )
    }
  })
  let sql = `${id} as (${etcSql} ${caseSql.join(',')} from ${
    children[0].from
  } group by ${groupName})`
  return sql
}

const handleGroupEtl = function (id, config, children) {
  const { groupColumn, groups, isGroup } = config
  let gsql = ''
  if (isGroup) {
    gsql = `group by ${children[0].from}.${groupColumn}`
  }
  let groupSql = groups.map((item) => {
    return `${item.type}(${children[0].from}.${item.column}) as ${item.alias}`
  })
  let sql = `${id} as (select ${
    isGroup ? groupColumn + ',' : ''
  } ${groupSql.join(',')} from ${children[0].from} ${gsql})`
  return sql
}

const handleAliasEtl = function (id, config, children) {
  const { fields } = config
  let table = `${children[0].from}`
  let aliasSql = fields.map((item) => {
    if (item) {
      return `${table}.${item.COLUMN_NAME} as ${item.alias}`
    }
  })
  let sql = `${id} as (select ${aliasSql.join(',')} from ${table})`
  return sql
}

/** format 
 * select to_char(current_timestamp, 'yyyy-mm-dd HH12:MI:SS')

select DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%S") as now; 

select TO_CHAR(current_timestamp, 'YYYY/MM/DD HH24:MI:ss') from dual
*/
const handleFormatDate = function (table, field, timeType, db_type) {
  let timeFormat = ''
  switch (db_type) {
    case 'MySQL':
      timeFormat = `DATE_FORMAT(${table}.${field},'${timeType}') as ${field}`
      break
    case 'PostgreSQL':
      timeFormat = `to_char(${table}.${field},'${timeType}') as ${field}`
      break
    case 'Oracle':
      timeFormat = `TO_CHAR(${table}.${field},'${timeType}') as ${field}`
      break
    default:
      break
  }
  return timeFormat
}
const handleFormateChar = function (
  table,
  field,
  db_type,
  isNum,
  numLen,
  isPrefix,
  prefix,
  isSuffix,
  suffix
) {
  let varFormatSql = ''
  if (isNum) {
    varFormatSql = `CAST(${table}.${field} as DECIMAL(10,${numLen})) as ${field}`
  } else {
    let concatSql = []
    if (isPrefix) {
      concatSql.push(`'${prefix}'`)
    }
    concatSql.push(`${table}.${field}`)
    if (isSuffix) {
      concatSql.push(`'${suffix}'`)
    }
    varFormatSql = `concat(${concatSql.join(',')}) as ${field}`
  }
  return varFormatSql
}

const handleFormateNumber = function (
  table,
  field,
  db_type,
  timeType,
  numberMethods,
  numTimeType,
  isPrefix,
  prefix,
  prefix,
  isSuffix,
  suffix,
  numLen
) {
  let numFormatSql = ''
  if (numberMethods === 'numTime') {
    switch (db_type) {
      case 'MySQL':
        numFormatSql = `FROM_UNIXTIME(${table}.${field}/${numTimeType},'${timeType}') as ${field}`
        break
      case 'PostgreSQL':
        numFormatSql = `TO_CHAR((to_timestamp(round(${table}.${field}/${numTimeType})) + interval '1 hour' * 8),'${timeType}') as ${field}`
        break
      case 'Oracle':
        numFormatSql = `TO_CHAR(${table}.${field} / (${numTimeType} * 60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), '${timeType}') as ${field}`
        break
      default:
        break
    }
  } else if (numberMethods === 'numDot') {
    numFormatSql = `CAST(${table}.${field} as DECIMAL(10,${numLen})) as ${field}`
  } else if (numberMethods === 'numString') {
    let concatSql = []
    if (isPrefix) {
      concatSql.push(`'${prefix}'`)
    }
    concatSql.push(`${table}.${field}`)
    if (isSuffix) {
      concatSql.push(`'${suffix}'`)
    }
    numFormatSql = `concat(${concatSql.join(',')}) as ${field}`
  }
  return numFormatSql
}
// select to_timestamp(round(1584764955000/1000)) + interval '1 hour' * 8
//  select FROM_UNIXTIME(1589857200,'%Y年%m月%d日')
const handleFormatEtl = function (id, config, children, db_type) {
  const {
    timeType,
    field,
    fields,
    type,
    isNum,
    numLen,
    isPrefix,
    prefix,
    isSuffix,
    suffix,
    numberMethods,
    numTimeType,
  } = config
  let table = `${children[0].from}`
  let formatSql = ''
  switch (type) {
    case 'date':
      formatSql = handleFormatDate(table, field, timeType, db_type)
      break
    case 'varchar':
      formatSql = handleFormateChar(
        table,
        field,
        db_type,
        isNum,
        numLen,
        isPrefix,
        prefix,
        isSuffix,
        suffix
      )
      break
    case 'number':
      formatSql = handleFormateNumber(
        table,
        field,
        db_type,
        timeType,
        numberMethods,
        numTimeType,
        isPrefix,
        prefix,
        prefix,
        isSuffix,
        suffix,
        numLen
      )
      break
    default:
      break
  }
  let fieldSql = []
  fields.forEach((item) => {
    if (item.alias) {
      if (item.alias === field) {
        fieldSql.push(`${formatSql}`)
      } else {
        fieldSql.push(`${table}.${item.alias}`)
      }
    }
  })
  let sql = `${id} as (select ${fieldSql.join(',')} from ${table})`
  return sql
}

const handleOutputEtl = function (id, config, children) {
  let table = `${children[0].from}`
  const { fields } = config
  let outSql = []
  fields.map((item) => {
    outSql.push(`${table}.${item.alias}`)
  })
  let sql = `${id} as (select ${outSql.join(',')} from ${table})`
  return sql
}
/**
 * 节点遍历运行
 * @param {*} node
 * @param {*} sql
 */
const generateSql = (node, db_type) => {
  const { id, config, type, children } = node
  let sql = ''
  switch (type) {
    case 'input':
      sql = handleInputEtl(id, config)
      break
    case 'compute':
      sql = handleComputeEtl(id, config, children)
      break
    case 'filter':
      sql = handleFilterEtl(id, config, children)
      break
    case 'merge':
      sql = handleMergeEtl(id, config, children)
      break
    case 'select':
      sql = handleSelectEtl(id, config, children)
      break
    case 'change':
      sql = handleValueChangeEtl(id, config, children)
      break
    case 'isnull':
      sql = handleIsNullEtl(id, config, children, db_type)
      break
    case 'distinct':
      sql = handleDistinctEtl(id, config, children, db_type)
      break
    case 'order':
      sql = handleOrderEtl(id, config, children)
      break
    case 'join':
      sql = handleJoinEtl(id, config, node.sourceNode, node.originNode)
      break
    case 'union':
      sql = handleUnionEtl(id, config, node.sourceNode, node.originNode)
      break
    case 'const':
      sql = handleConstEtl(id, config, children)
      break
    case 'ctr':
      sql = handleCToREtl(id, config, children)
      break
    case 'rtc':
      sql = handleRToCEtl(id, config, children)
      break
    case 'group':
      sql = handleGroupEtl(id, config, children)
      break
    case 'alias':
      sql = handleAliasEtl(id, config, children)
      break
    case 'format':
      sql = handleFormatEtl(id, config, children, db_type)
      break
    case 'output':
      sql = handleOutputEtl(id, config, children)
      break
    case 'congroup':
      sql = handleCongroupEtl(id, config, children)
      break
    default:
      break
  }
  return sql
}
const etlNodeRunAction = (node, db_type) => {
  let sql = 'With '
  function walk(node, db_type) {
    if (node.children != undefined) {
      node.children.forEach(function (child) {
        walk(child, db_type)
      })
      sql = sql + generateSql(node, db_type) + ','
    }
  }
  walk(node, db_type)
  return sql.substring(0, sql.length - 1) + `select * from ${node.id}`
}
module.exports = {
  etlNodeRunAction,
}
